1077. Project Employees III.sql

SELECTp.project_id,p.employee_id
FROM project_1077 p
JOIN employee_1077 e ONp.employee_id=e.employee_id
WHERE (p.project_id,e.experience_years) IN (SELECTp.project_id,MAX(e.experience_years)
FROM project_1077 p
JOIN employee_1077 e ONp.employee_id=e.employee_id
GROUP BYp.project_id)
ORDER BY1;


--(OR)


WITH ranked AS(
SELECTp.project_id,p.employee_id,
 DENSE_RANK() OVER (w) rnk
FROM project_1077 p
JOIN employee_1077 e ONp.employee_id=e.employee_id
 WINDOW w AS (PARTITION BY p.project_idORDER BYe.experience_yearsDESC)
)

SELECT project_id,employee_id
FROM ranked
WHERE rnk =1;